/**
 * 统计控制器
 * 处理各种数据统计和分析相关的数据库操作
 */

import { BaseController, ensureDbInit, createResponse, createErrorResponse } from './BaseController'
import database from '../../utils/database'
import { generateId } from '../../mock/utils'

export class StatsController extends BaseController {
  constructor() {
    super('stats')
  }
  
  /**
   * 获取仪表板统计数据
   */
  async getDashboardStats() {
    try {
      await ensureDbInit()
      
      // 并行获取各种统计数据
      const [habitsStats, todosStats, financeStats, timeStats] = await Promise.all([
        this.getHabitsStats(),
        this.getTodosStats(),
        this.getFinanceStats(),
        this.getTimeStats()
      ])
      
      return createResponse({
        habits: habitsStats.data,
        todos: todosStats.data,
        finance: financeStats.data,
        time: timeStats.data,
        lastUpdated: new Date().toISOString()
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取仪表板统计失败')
    }
  }
  
  /**
   * 获取习惯统计
   */
  async getHabitsStats(params: {
    startDate?: string
    endDate?: string
  } = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate } = params
      
      let dateFilter = ''
      let dateParams: any[] = []
      
      if (startDate && endDate) {
        dateFilter = ' AND date BETWEEN ? AND ?'
        dateParams = [startDate, endDate]
      }
      
      // 总习惯数
      const totalHabitsResult = await database.query(
        'SELECT COUNT(*) as count FROM habits WHERE user_id = ? AND is_deleted = 0',
        ['current_user']
      )
      
      // 活跃习惯数
      const activeHabitsResult = await database.query(
        'SELECT COUNT(*) as count FROM habits WHERE user_id = ? AND is_active = 1 AND is_deleted = 0',
        ['current_user']
      )
      
      // 今日完成的习惯数
      const today = new Date().toISOString().split('T')[0]
      const todayCompletedResult = await database.query(
        'SELECT COUNT(DISTINCT habit_id) as count FROM habit_records WHERE user_id = ? AND date = ? AND status = ?',
        ['current_user', today, 'completed']
      )
      
      // 本周完成统计
      const weekStart = this.getWeekStart(new Date()).toISOString().split('T')[0]
      const weekEnd = new Date().toISOString().split('T')[0]
      const weekCompletedResult = await database.query(
        `SELECT 
          date,
          COUNT(DISTINCT habit_id) as completed_count
        FROM habit_records 
        WHERE user_id = ? AND date BETWEEN ? AND ? AND status = ?
        GROUP BY date
        ORDER BY date`,
        ['current_user', weekStart, weekEnd, 'completed']
      )
      
      // 习惯完成率统计
      const completionRateResult = await database.query(
        `SELECT 
          h.id,
          h.name,
          COUNT(hr.id) as total_records,
          SUM(CASE WHEN hr.status = 'completed' THEN 1 ELSE 0 END) as completed_records
        FROM habits h
        LEFT JOIN habit_records hr ON h.id = hr.habit_id AND hr.user_id = ? ${dateFilter}
        WHERE h.user_id = ? AND h.is_deleted = 0
        GROUP BY h.id, h.name`,
        ['current_user', ...dateParams, 'current_user']
      )
      
      const totalHabits = totalHabitsResult.data?.[0]?.count || 0
      const activeHabits = activeHabitsResult.data?.[0]?.count || 0
      const todayCompleted = todayCompletedResult.data?.[0]?.count || 0
      const weekCompleted = weekCompletedResult.data || []
      const completionRates = (completionRateResult.data || []).map((item: any) => ({
        habitId: item.id,
        habitName: item.name,
        totalRecords: item.total_records || 0,
        completedRecords: item.completed_records || 0,
        completionRate: item.total_records > 0 ? Math.round((item.completed_records / item.total_records) * 100) : 0
      }))
      
      return createResponse({
        totalHabits,
        activeHabits,
        todayCompleted,
        todayCompletionRate: activeHabits > 0 ? Math.round((todayCompleted / activeHabits) * 100) : 0,
        weekCompleted,
        completionRates
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取习惯统计失败')
    }
  }
  
  /**
   * 获取待办事项统计
   */
  async getTodosStats(params: {
    startDate?: string
    endDate?: string
  } = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate } = params
      
      let dateFilter = ''
      let dateParams: any[] = []
      
      if (startDate && endDate) {
        dateFilter = ' AND created_at BETWEEN ? AND ?'
        dateParams = [startDate, endDate]
      }
      
      // 总待办事项数
      const totalTodosResult = await database.query(
        `SELECT COUNT(*) as count FROM todos WHERE user_id = ? AND is_deleted = 0 ${dateFilter}`,
        ['current_user', ...dateParams]
      )
      
      // 按状态统计
      const statusStatsResult = await database.query(
        `SELECT 
          status,
          COUNT(*) as count
        FROM todos 
        WHERE user_id = ? AND is_deleted = 0 ${dateFilter}
        GROUP BY status`,
        ['current_user', ...dateParams]
      )
      
      // 按优先级统计
      const priorityStatsResult = await database.query(
        `SELECT 
          priority,
          COUNT(*) as count
        FROM todos 
        WHERE user_id = ? AND is_deleted = 0 ${dateFilter}
        GROUP BY priority`,
        ['current_user', ...dateParams]
      )
      
      // 逾期统计
      const today = new Date().toISOString().split('T')[0]
      const overdueResult = await database.query(
        `SELECT COUNT(*) as count FROM todos 
        WHERE user_id = ? AND is_deleted = 0 AND due_date < ? AND status != 'completed' ${dateFilter}`,
        ['current_user', today, ...dateParams]
      )
      
      // 今日完成统计
      const todayCompletedResult = await database.query(
        `SELECT COUNT(*) as count FROM todos 
        WHERE user_id = ? AND is_deleted = 0 AND status = 'completed' AND DATE(completed_at) = ?`,
        ['current_user', today]
      )
      
      const totalTodos = totalTodosResult.data?.[0]?.count || 0
      const statusStats = statusStatsResult.data || []
      const priorityStats = priorityStatsResult.data || []
      const overdue = overdueResult.data?.[0]?.count || 0
      const todayCompleted = todayCompletedResult.data?.[0]?.count || 0
      
      // 格式化状态统计
      const formattedStatusStats = {
        pending: 0,
        completed: 0,
        cancelled: 0
      }
      
      statusStats.forEach((item: any) => {
        if (item.status && formattedStatusStats.hasOwnProperty(item.status)) {
          formattedStatusStats[item.status as keyof typeof formattedStatusStats] = item.count
        }
      })
      
      // 格式化优先级统计
      const formattedPriorityStats = {
        high: 0,
        medium: 0,
        low: 0
      }
      
      priorityStats.forEach((item: any) => {
        if (item.priority && formattedPriorityStats.hasOwnProperty(item.priority)) {
          formattedPriorityStats[item.priority as keyof typeof formattedPriorityStats] = item.count
        }
      })
      
      return createResponse({
        totalTodos,
        statusStats: formattedStatusStats,
        priorityStats: formattedPriorityStats,
        overdue,
        todayCompleted,
        completionRate: totalTodos > 0 ? Math.round((formattedStatusStats.completed / totalTodos) * 100) : 0
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取待办事项统计失败')
    }
  }
  
  /**
   * 获取财务统计
   */
  async getFinanceStats(params: {
    startDate?: string
    endDate?: string
    accountId?: string
  } = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate, accountId } = params
      
      let dateFilter = ''
      let accountFilter = ''
      let filterParams: any[] = []
      
      if (startDate && endDate) {
        dateFilter = ' AND date BETWEEN ? AND ?'
        filterParams.push(startDate, endDate)
      }
      
      if (accountId) {
        accountFilter = ' AND account_id = ?'
        filterParams.push(accountId)
      }
      
      // 收入统计
      const incomeResult = await database.query(
        `SELECT COALESCE(SUM(amount), 0) as total FROM finance_transactions 
        WHERE user_id = ? AND type = 'income' AND is_deleted = 0 ${dateFilter} ${accountFilter}`,
        ['current_user', ...filterParams]
      )
      
      // 支出统计
      const expenseResult = await database.query(
        `SELECT COALESCE(SUM(amount), 0) as total FROM finance_transactions 
        WHERE user_id = ? AND type = 'expense' AND is_deleted = 0 ${dateFilter} ${accountFilter}`,
        ['current_user', ...filterParams]
      )
      
      // 账户余额统计
      const balanceResult = await database.query(
        `SELECT COALESCE(SUM(balance), 0) as total FROM finance_accounts 
        WHERE user_id = ? AND is_active = 1 AND is_deleted = 0`,
        ['current_user']
      )
      
      // 交易数量统计
      const transactionCountResult = await database.query(
        `SELECT COUNT(*) as count FROM finance_transactions 
        WHERE user_id = ? AND is_deleted = 0 ${dateFilter} ${accountFilter}`,
        ['current_user', ...filterParams]
      )
      
      // 按分类统计支出
      const categoryExpenseResult = await database.query(
        `SELECT 
          category_id,
          SUM(amount) as total
        FROM finance_transactions 
        WHERE user_id = ? AND type = 'expense' AND is_deleted = 0 ${dateFilter} ${accountFilter}
        GROUP BY category_id
        ORDER BY total DESC`,
        ['current_user', ...filterParams]
      )
      
      // 每日收支趋势（最近30天）
      const trendStartDate = new Date()
      trendStartDate.setDate(trendStartDate.getDate() - 30)
      const trendStart = trendStartDate.toISOString().split('T')[0]
      const trendEnd = new Date().toISOString().split('T')[0]
      
      const trendResult = await database.query(
        `SELECT 
          date,
          SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) as income,
          SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) as expense
        FROM finance_transactions 
        WHERE user_id = ? AND is_deleted = 0 AND date BETWEEN ? AND ? ${accountFilter}
        GROUP BY date
        ORDER BY date`,
        ['current_user', trendStart, trendEnd, ...(accountId ? [accountId] : [])]
      )
      
      const totalIncome = incomeResult.data?.[0]?.total || 0
      const totalExpense = expenseResult.data?.[0]?.total || 0
      const totalBalance = balanceResult.data?.[0]?.total || 0
      const transactionCount = transactionCountResult.data?.[0]?.count || 0
      const categoryExpenses = categoryExpenseResult.data || []
      const dailyTrend = trendResult.data || []
      
      return createResponse({
        totalIncome,
        totalExpense,
        netIncome: totalIncome - totalExpense,
        totalBalance,
        transactionCount,
        categoryExpenses,
        dailyTrend,
        averageDaily: {
          income: dailyTrend.length > 0 ? totalIncome / dailyTrend.length : 0,
          expense: dailyTrend.length > 0 ? totalExpense / dailyTrend.length : 0
        }
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取财务统计失败')
    }
  }
  
  /**
   * 获取时间统计
   */
  async getTimeStats(params: {
    startDate?: string
    endDate?: string
    activityId?: string
  } = {}) {
    try {
      await ensureDbInit()
      const { startDate, endDate, activityId } = params
      
      let dateFilter = ''
      let activityFilter = ''
      let filterParams: any[] = []
      
      if (startDate && endDate) {
        dateFilter = ' AND start_time BETWEEN ? AND ?'
        filterParams.push(startDate, endDate)
      }
      
      if (activityId) {
        activityFilter = ' AND activity_id = ?'
        filterParams.push(activityId)
      }
      
      // 总时长统计
      const totalTimeResult = await database.query(
        `SELECT COALESCE(SUM(duration), 0) as total FROM time_records 
        WHERE user_id = ? AND status = 'completed' AND is_deleted = 0 ${dateFilter} ${activityFilter}`,
        ['current_user', ...filterParams]
      )
      
      // 记录数量统计
      const recordCountResult = await database.query(
        `SELECT COUNT(*) as count FROM time_records 
        WHERE user_id = ? AND status = 'completed' AND is_deleted = 0 ${dateFilter} ${activityFilter}`,
        ['current_user', ...filterParams]
      )
      
      // 按活动分类统计
      const activityStatsResult = await database.query(
        `SELECT 
          activity_id,
          COUNT(*) as record_count,
          SUM(duration) as total_duration
        FROM time_records 
        WHERE user_id = ? AND status = 'completed' AND is_deleted = 0 ${dateFilter} ${activityFilter}
        GROUP BY activity_id
        ORDER BY total_duration DESC`,
        ['current_user', ...filterParams]
      )
      
      // 每日时间趋势（最近30天）
      const trendStartDate = new Date()
      trendStartDate.setDate(trendStartDate.getDate() - 30)
      const trendStart = trendStartDate.toISOString().split('T')[0]
      const trendEnd = new Date().toISOString().split('T')[0]
      
      const dailyTrendResult = await database.query(
        `SELECT 
          DATE(start_time) as date,
          COUNT(*) as record_count,
          SUM(duration) as total_duration
        FROM time_records 
        WHERE user_id = ? AND status = 'completed' AND is_deleted = 0 
        AND DATE(start_time) BETWEEN ? AND ? ${activityFilter}
        GROUP BY DATE(start_time)
        ORDER BY date`,
        ['current_user', trendStart, trendEnd, ...(activityId ? [activityId] : [])]
      )
      
      // 今日统计
      const today = new Date().toISOString().split('T')[0]
      const todayStatsResult = await database.query(
        `SELECT 
          COUNT(*) as record_count,
          COALESCE(SUM(duration), 0) as total_duration
        FROM time_records 
        WHERE user_id = ? AND status = 'completed' AND is_deleted = 0 
        AND DATE(start_time) = ? ${activityFilter}`,
        ['current_user', today, ...(activityId ? [activityId] : [])]
      )
      
      const totalTime = totalTimeResult.data?.[0]?.total || 0
      const recordCount = recordCountResult.data?.[0]?.count || 0
      const activityStats = activityStatsResult.data || []
      const dailyTrend = dailyTrendResult.data || []
      const todayStats = todayStatsResult.data?.[0] || { record_count: 0, total_duration: 0 }
      
      return createResponse({
        totalTime,
        recordCount,
        averageTime: recordCount > 0 ? Math.round(totalTime / recordCount) : 0,
        activityStats,
        dailyTrend,
        todayStats: {
          recordCount: todayStats.record_count,
          totalDuration: todayStats.total_duration
        },
        productivity: {
          dailyAverage: dailyTrend.length > 0 ? Math.round(totalTime / dailyTrend.length) : 0,
          weeklyTotal: this.calculateWeeklyTotal(dailyTrend)
        }
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取时间统计失败')
    }
  }
  
  /**
   * 获取综合生产力报告
   */
  async getProductivityReport(params: {
    startDate?: string
    endDate?: string
  } = {}) {
    try {
      const { startDate, endDate } = params
      
      // 并行获取各模块数据
      const [habitsData, todosData, timeData] = await Promise.all([
        this.getHabitsStats({ startDate, endDate }),
        this.getTodosStats({ startDate, endDate }),
        this.getTimeStats({ startDate, endDate })
      ])
      
      if (habitsData.code !== 0 || todosData.code !== 0 || timeData.code !== 0) {
        return createErrorResponse('获取生产力报告数据失败')
      }
      
      const habits = habitsData.data
      const todos = todosData.data
      const time = timeData.data
      
      // 计算综合生产力评分
      const habitScore = this.calculateHabitScore(habits)
      const todoScore = this.calculateTodoScore(todos)
      const timeScore = this.calculateTimeScore(time)
      
      const overallScore = Math.round((habitScore + todoScore + timeScore) / 3)
      
      // 生成建议
      const suggestions = this.generateProductivitySuggestions({
        habits,
        todos,
        time,
        scores: { habitScore, todoScore, timeScore, overallScore }
      })
      
      return createResponse({
        period: {
          startDate: startDate || this.getMonthStart().toISOString().split('T')[0],
          endDate: endDate || new Date().toISOString().split('T')[0]
        },
        scores: {
          overall: overallScore,
          habits: habitScore,
          todos: todoScore,
          time: timeScore
        },
        summary: {
          habitsCompleted: habits.todayCompleted,
          todosCompleted: todos.todayCompleted,
          timeTracked: time.todayStats.totalDuration,
          productivity: this.getProductivityLevel(overallScore)
        },
        suggestions,
        generatedAt: new Date().toISOString()
      })
    } catch (error: any) {
      return createErrorResponse(error.message || '获取生产力报告失败')
    }
  }
  
  /**
   * 计算习惯评分
   */
  private calculateHabitScore(habitsData: any): number {
    const { todayCompletionRate, activeHabits } = habitsData
    
    if (activeHabits === 0) return 0
    
    // 基于完成率计算评分
    let score = todayCompletionRate
    
    // 活跃习惯数量加分
    if (activeHabits >= 5) score += 10
    else if (activeHabits >= 3) score += 5
    
    return Math.min(score, 100)
  }
  
  /**
   * 计算待办事项评分
   */
  private calculateTodoScore(todosData: any): number {
    const { completionRate, overdue, todayCompleted } = todosData
    
    let score = completionRate
    
    // 今日完成加分
    if (todayCompleted >= 5) score += 15
    else if (todayCompleted >= 3) score += 10
    else if (todayCompleted >= 1) score += 5
    
    // 逾期扣分
    if (overdue > 0) {
      score -= Math.min(overdue * 5, 30)
    }
    
    return Math.max(Math.min(score, 100), 0)
  }
  
  /**
   * 计算时间评分
   */
  private calculateTimeScore(timeData: any): number {
    const { todayStats, productivity } = timeData
    
    let score = 0
    
    // 基于今日记录时间计算评分
    const todayHours = todayStats.totalDuration / 3600
    if (todayHours >= 8) score = 100
    else if (todayHours >= 6) score = 80
    else if (todayHours >= 4) score = 60
    else if (todayHours >= 2) score = 40
    else if (todayHours >= 1) score = 20
    
    // 记录数量加分
    if (todayStats.recordCount >= 5) score += 10
    else if (todayStats.recordCount >= 3) score += 5
    
    return Math.min(score, 100)
  }
  
  /**
   * 生成生产力建议
   */
  private generateProductivitySuggestions(data: any): string[] {
    const suggestions: string[] = []
    const { habits, todos, time, scores } = data
    
    // 习惯相关建议
    if (scores.habitScore < 60) {
      suggestions.push('建议增加每日习惯的完成率，可以从简单的习惯开始')
    }
    if (habits.activeHabits < 3) {
      suggestions.push('考虑添加更多有益的日常习惯来提升生活质量')
    }
    
    // 待办事项相关建议
    if (todos.overdue > 0) {
      suggestions.push(`您有 ${todos.overdue} 个逾期任务，建议优先处理`)
    }
    if (scores.todoScore < 60) {
      suggestions.push('提高任务完成效率，可以尝试番茄工作法或时间块管理')
    }
    
    // 时间管理相关建议
    if (time.todayStats.totalDuration < 3600) {
      suggestions.push('建议增加时间记录，更好地了解时间分配情况')
    }
    if (scores.timeScore < 60) {
      suggestions.push('优化时间管理，专注于高价值活动')
    }
    
    // 综合建议
    if (scores.overallScore >= 80) {
      suggestions.push('您的生产力表现优秀，继续保持！')
    } else if (scores.overallScore >= 60) {
      suggestions.push('生产力表现良好，还有提升空间')
    } else {
      suggestions.push('建议制定更清晰的目标和计划来提升生产力')
    }
    
    return suggestions
  }
  
  /**
   * 获取生产力等级
   */
  private getProductivityLevel(score: number): string {
    if (score >= 90) return '优秀'
    if (score >= 80) return '良好'
    if (score >= 70) return '中等'
    if (score >= 60) return '一般'
    return '需要改进'
  }
  
  /**
   * 获取周开始日期
   */
  private getWeekStart(date: Date): Date {
    const d = new Date(date)
    const day = d.getDay()
    const diff = d.getDate() - day + (day === 0 ? -6 : 1) // 周一为一周开始
    return new Date(d.setDate(diff))
  }
  
  /**
   * 获取月开始日期
   */
  private getMonthStart(): Date {
    const now = new Date()
    return new Date(now.getFullYear(), now.getMonth(), 1)
  }
  
  /**
   * 计算周总计
   */
  private calculateWeeklyTotal(dailyTrend: any[]): number {
    const now = new Date()
    const weekStart = this.getWeekStart(now)
    const weekStartStr = weekStart.toISOString().split('T')[0]
    
    return dailyTrend
      .filter(item => item.date >= weekStartStr)
      .reduce((total, item) => total + (item.total_duration || 0), 0)
  }
}

// 导出单例实例
export const statsController = new StatsController()